<%@ Page language="vb" CodeFile="searchInvoice.aspx.vb" Inherits="searchInvoice_aspx_vb" %>

<%
    rowNumber = 1
    If Request.QueryString("do") = Nothing Then
        subDo = Request.QueryString("subDo")
        dbOpen()
        RecSet = Conn.Execute("Select partnerID, partnerName, countryID From tblPartner Order by partnerName")
        If Not RecSet.EOF Then
            Do Until RecSet.EOF
                partnerID = RecSet.Fields("partnerID").Value
                partnerName = RecSet.Fields("partnerName").Value
                countryID = RecSet.Fields("countryID").Value
                strSelectSubJS = strSelectSubJS + "new Array(" & partnerID & "," & countryID & ",""" & partnerName & """),"
                RecSet.MoveNext()
            Loop
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        dbOpen()
        RecSet = Conn.Execute("Select countryID From tblCountry Order by countryID")
        Do Until RecSet.EOF
            countryID = RecSet.Fields("countryID").Value
            strSelectSubJS = strSelectSubJS + "new Array(0," & countryID & ","" ------------------------------------  ""),"
            RecSet.MoveNext()
        Loop
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        strSelectSubJS = Left(strSelectSubJS, Len(strSelectSubJS) - 1)
        strSelectSubHeadline = "Select partner"
        selectSub()
        Over()
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='100%'>")
        Response.Write("<tr>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='3'>SEARCH LEASE INVOICE<hr class='hrHeadline'></td></tr>")
        Response.Write("<form action='searchInvoice.aspx?subDo=search' method='post' name='store_productsSearch'>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Country:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='countryID' id='countryID' onchange=""set_child_listbox(this, document.store_productsSearch.partnerID,Product,'Products');"">")
        Response.Write("<option value=''> Select country  ")
        dbOpen()
        RecSet = Conn.Execute("Select countryID, country From tblCountry")
        If Not RecSet.EOF Then
            Do Until RecSet.EOF
                countryID = RecSet.Fields("countryID").Value
                country = RecSet.Fields("country").Value
                Response.Write("<option value='" & countryID & "'> " & country & " ")
                RecSet.MoveNext()
            Loop
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("<td><div id='error_countryID' class='edit_error'>&laquo; OBS!</div></td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Partner:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='partnerID' id='partnerID' disabled>")
        Response.Write("<option value=''> - Select partner - ")
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("<td><div id='error_partnerID' class='edit_error'>&laquo; OBS!</div></td>")
        Response.Write("</tr>")
        Response.Write("<tr><td class='formBold'>Invoice ID:</td><td align='right'><input type='text' name='invoiceID' id='invoiceID' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>Invoice date (YYYY-MM-DD):</td><td align='right'><input type='text' name='invoiceDateFrom' id='invoiceDateFrom' size='17' maxlength='10'onkeyup='re=/([\d\-]*)/; re.exec(this.value); this.value=RegExp.$1;'> - <input type='text' name='invoiceDateTo' id='invoiceDateTo' size='17' maxlength='10'onkeyup='re=/([\d\-]*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>Payment date (YYYY-MM-DD):</td><td align='right'><input type='text' name='paymentDateFrom' id='paymentDateFrom' size='17' maxlength='10'onkeyup='re=/([\d\-]*)/; re.exec(this.value); this.value=RegExp.$1;'> - <input type='text' name='paymentDateTo' id='paymentDateTo' size='17' maxlength='10'onkeyup='re=/([\d\-]*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td align='right' colspan='2'><input type='submit' value='Search'></td></tr>")
        Response.Write("</form>")
        Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("<td width='100'></td>")
        Response.Write("<td valign='top'>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        Response.Write("<tr>")
        Response.Write("<td valign='top' colspan='3'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
        Response.Write("<tr><td class='headline' colspan='12'>SEARCH RESULT<hr class='hrHeadline'></td></tr>")
        countryID = Request.Form("countryID")
        partnerID = Request.Form("partnerID")
        invoiceID = Request.Form("invoiceID")
        invoiceDateFrom = Request.Form("invoiceDateFrom")
        invoiceDateTo = Request.Form("invoiceDateTo")
        paymentDateFrom = Request.Form("paymentDateFrom")
        paymentDateTO = Request.Form("paymentDateTo")
        If Len(countryID) = 0 And Len(partnerID) = 0 And Len(invoiceID) = 0 And Len(invoiceDateFrom) = 0 And Len(invoiceDateTo) = 0 And Len(paymentDateFrom) = 0 And Len(paymentDateTO) = 0 Then
            countryID = Request.QueryString("countryID")
            partnerID = Request.QueryString("partnerID")
            invoiceID = Request.QueryString("invoiceID")
            invoiceDateFrom = Request.QueryString("invoiceDateFrom")
            paymentDateTO = Request.QueryString("paymentDateTo")
            invoiceDateFrom = Request.QueryString("invoiceDateFrom")
            paymentDateTO = Request.QueryString("paymentDateTo")
        End If
        If Len(countryID) > 0 Then
            countryID = CLng(countryID)
            sqlCountryID = " And i.countryID = " & countryID & ""
        End If
        If Len(partnerID) > 0 Then
            partnerID = CLng(partnerID)
            sqlPartnerID = " And i.partnerID = " & partnerID & ""
        End If
        If Len(invoiceID) > 0 Then
            invoiceID = CLng(invoiceID)
            sqlInvoiceID = " And i.invoiceID = " & invoiceID & ""
        End If
        If Len(invoiceDateFrom) > 0 And Len(invoiceDateTo) > 0 Then
            sqlInvoiceDate = " And i.invoiceDate BETWEEN '" & invoiceDateFrom & "' AND '" & invoiceDateTo & "'"
        End If
        If Len(paymentDateFrom) > 0 And Len(paymentDateTO) > 0 Then
            sqlPaymentDate = " And i.paymentDate BETWEEN '" & paymentDateFrom & "' AND '" & paymentDateTO & "'"
        End If
        If Len(subDo) = 0 Then
            sqlCountryID = " And i.countryID = 1000"
        End If
        dbOpen()
        RecSet = Conn.Execute("Select i.invoiceID, i.invoiceAmount, i.invoiceClaim, i.invoiceDate, i.expiryDate, i.paymentDate, i.invoicePrinted, i.invoiceSettled, p.partnerName, c.country, c.countryCurrency, c.decimals, ic.invoiceAmount As invoiceCreditAmount From (((tblInvoice i INNER JOIN tblPartner p ON p.partnerID = i.partnerID) INNER JOIN tblCountry c ON c.countryID = i.countryID) LEFT JOIN tblInvoiceCredit ic ON ic.invoiceID = i.invoiceID) Where i.invoiceID > 0" & sqlCountryID & sqlPartnerID & sqlInvoiceID & sqlInvoiceDate & sqlPaymentDate & " Order By i.invoiceID")
        If RecSet.EOF Then
            Response.Write("<tr><td><font color='red'>No invoices found.</font></td></tr>")
        Else
            Response.Write("<tr bgcolor='#eeeeee'>")
            Response.Write("<td class='formBold'>Invoice ID</td>")
            Response.Write("<td class='formBold'>Partner</td>")
            Response.Write("<td class='formBold' align='right'>Invoice Amount (Tax excl.)</td>")
            Response.Write("<td class='formBold' align='right'>Invoice claim (Tax excl.)</td>")
            Response.Write("<td class='formBold' align='right'>Invoice Date</td>")
            Response.Write("<td class='formBold' align='right'>Expiry date</td>")
            Response.Write("<td class='formBold' align='right'>Days</td>")
            Response.Write("<td class='formBold' align='right'>Payment Date</td>")
            Response.Write("<td class='formBold' align='right'>Status</td>")
            Response.Write("<td class='formBold' align='right'>Country</td>")
            Response.Write("<td class='formBold' align='right' colspan='2'></td>")
            Response.Write("</tr>")
            Do Until RecSet.EOF
                dbInvoiceID = RecSet.Fields("invoiceID").Value
                invoiceAmount = RecSet.Fields("invoiceAmount").Value
                invoiceClaim = RecSet.Fields("invoiceClaim").Value
                invoiceDate = RecSet.Fields("invoiceDate").Value
                expiryDate = RecSet.Fields("expiryDate").Value
                paymentDate = RecSet.Fields("paymentDate").Value
                invoicePrinted = RecSet.Fields("invoicePrinted").Value
                invoiceSettled = RecSet.Fields("invoiceSettled").Value
                partnerName = RecSet.Fields("partnerName").Value
                country = RecSet.Fields("country").Value
                countryCurrency = RecSet.Fields("countryCurrency").Value
                decimals = RecSet.Fields("decimals").Value
                invoiceCreditAmount = RecSet.Fields("invoiceCreditAmount").Value
                If IsDBNull(invoiceCreditAmount) = False Then
                    invoiceAmount = CDbl(invoiceAmount) - CDbl(invoiceCreditAmount)
                    If CInt(invoiceClaim) > 0 Then
                        invoiceClaim = CDbl(invoiceClaim) - CDbl(invoiceCreditAmount)
                    End If
                End If
                invoiceAmount = FormatNumber(CDbl(invoiceAmount), decimals)
                invoiceAmount = CDbl(invoiceAmount)
                invoiceClaim = FormatNumber(CDbl(invoiceClaim), decimals)
                invoiceClaim = CDbl(invoiceClaim)
                totInvoiceAmount = totInvoiceAmount + invoiceAmount
                totInvoiceClaim = totInvoiceClaim + invoiceClaim
                If IsDBNull(invoiceDate) Then
                    invoiceStatus = "<font color='red'>not activated</font>"
                ElseIf CDbl(invoiceSettled) = 1 Then
                    invoiceStatus = "<font color='green'>settled</font>"
                ElseIf CDbl(invoicePrinted) = 0 Then
                    invoiceStatus = "queued for printing"
                ElseIf CDbl(invoicePrinted) = 1 Then
                    invoiceStatus = "printed"
                End If
                If IsDBNull(invoiceDate) Then
                    invoiceDate = "N/A"
                End If
                If IsDBNull(expiryDate) = False And IsDBNull(paymentDate) = True Then
                    expiryDays = DateDiff("d", Now(), Convert.ToDateTime(expiryDate))
                    If expiryDays >= 0 Then
                        expiryDays = "<font color='green'>" & expiryDays & "</font>"
                    Else
                        expiryDays = "<font color='red'>" & expiryDays & "</font>"
                    End If
                ElseIf IsDBNull(expiryDate) = True Then
                    expiryDate = "N/A"
                    expiryDays = "N/A"
                Else
                    expiryDays = "N/A"
                End If
                If IsDBNull(paymentDate) Then
                    paymentDate = "N/A"
                End If
                If rowNumber = 0 Then
                    strGrey = " bgcolor='#eeeeee'"
                    rowNumber = 1
                Else
                    strGrey = ""
                    rowNumber = 0
                End If
                Response.Write("<tr" & strGrey & ">")
                Response.Write("<td>" & dbInvoiceID & "</td>")
                Response.Write("<td>" & partnerName & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(invoiceAmount), 2) & " " & countryCurrency & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(invoiceClaim), 2) & " " & countryCurrency & "</td>")
                Response.Write("<td align='right'>" & invoiceDate & "</td>")
                Response.Write("<td align='right'>" & expiryDate & "</td>")
                Response.Write("<td align='right'>" & expiryDays & "</td>")
                Response.Write("<td align='right'>" & paymentDate & "</td>")
                Response.Write("<td align='right'>" & invoiceStatus & "</td>")
                Response.Write("<td align='right'>" & country & "</td>")
                Response.Write("<td align='right'><a href='searchInvoice.aspx?subDo=content&invoiceID=" & dbInvoiceID & "'>view details</a></td>")
                Response.Write("<td align='right'><a href='printInvoice.aspx?invoiceID=" & dbInvoiceID & "' target='_blank'>print invoice</a></td>")
                Response.Write("</tr>")
                RecSet.MoveNext()
            Loop
            If rowNumber = 0 Then
                strGrey = " bgcolor='#eeeeee'"
                rowNumber = 1
            Else
                strGrey = ""
                rowNumber = 0
            End If
            Response.Write("<tr" & strGrey & ">")
            Response.Write("<td class='formBold'>Total sum:</td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(CDbl(totInvoiceAmount), 2) & " EUR</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(CDbl(totInvoiceClaim), 2) & " EUR</td>")
            Response.Write("<td class='formBold' colspan='8'></td>")
            Response.Write("</tr>")
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<tr><td colspan='12'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        If subDo = "content" Then
            invoiceID = Request.QueryString("invoiceID")
            invoiceID = CLng(invoiceID)
            Response.Write("<tr>")
            Response.Write("<td valign='top' colspan='3'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
            Response.Write("<tr><td class='headline' colspan='9'>THIS INVOICE CONTAINS THE FOLLOWING VEHICLES<hr class='hrHeadline'></td></tr>")
            dbOpen()
            RecSet = Conn.Execute("Select ic.leaseCurrentDate, v.vehicleID, v.regnr, v.vinNumber, v.contractNumber, v.date, p.product, pl.price, b.brand, m.model, d.dealer, (Select Sum(ap.price) From (tblInvoiceContentAdditional ica INNER JOIN tblAdditionalProduct ap ON ap.additionalProductID = ica.additionalProductID) Where ica.vehicleID = v.vehicleID And ica.invoiceID = ic.invoiceID) As additionalProductSum From ((((((tblInvoiceContent ic INNER JOIN tblVehicle v ON v.vehicleID = ic.vehicleID) INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblPriceList pl ON pl.priceListID = ic.priceListID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblDealer d ON d.dealerID = v.dealerID) Where ic.invoiceID = 100061")
            If RecSet.EOF Then
                Response.Write("<tr><td><font color='red'>ERROR</font></td></tr>")
            Else
                Response.Write("<tr bgcolor='#eeeeee'>")
                Response.Write("<td class='formBold'>Car dealer</td>")
                Response.Write("<td class='formBold'>Contract number</td>")
                Response.Write("<td class='formBold'>Licence number</td>")
                Response.Write("<td class='formBold'>VIN-number</td>")
                Response.Write("<td class='formBold'>Product</td>")
                Response.Write("<td class='formBold'>Period</td>")
                Response.Write("<td class='formBold' align='right'>Price</td>")
                Response.Write("<td class='formBold' align='right'>First created</td>")
                Response.Write("<td class='formBold' align='right'></td>")
                Response.Write("</tr>")
                rowNumber = 1
                Do Until RecSet.EOF
                    leaseCurrentDate = RecSet.Fields("leaseCurrentDate").Value
                    vehicleID = RecSet.Fields("vehicleID").Value
                    regnr = RecSet.Fields("regnr").Value
                    vinNumber = RecSet.Fields("vinNumber").Value
                    contractNumber = RecSet.Fields("contractNumber").Value
                    __date = RecSet.Fields("date").Value
                    product = RecSet.Fields("product").Value
                    price = RecSet.Fields("price").Value
                    brand = RecSet.Fields("brand").Value
                    model = RecSet.Fields("model").Value
                    dealer = RecSet.Fields("dealer").Value
                    additionalProductSum = RecSet.Fields("additionalProductSum").Value
                    If IsDBNull(additionalProductSum) = True Then
                        additionalProductSum = 0
                    End If
                    leaseCurrentDate = Left(leaseCurrentDate, 7)
                    price = price + additionalProductSum
                    If rowNumber = 0 Then
                        strGrey = " bgcolor='#eeeeee'"
                        rowNumber = 1
                    Else
                        strGrey = ""
                        rowNumber = 0
                    End If
                    Response.Write("<tr" & strGrey & ">")
                    Response.Write("<td>" & dealer & "</td>")
                    Response.Write("<td>" & contractNumber & "</td>")
                    Response.Write("<td>" & regnr & "</td>")
                    Response.Write("<td>" & vinNumber & "</td>")
                    Response.Write("<td>" & product & "</td>")
                    Response.Write("<td>" & leaseCurrentDate & "</td>")
                    Response.Write("<td align='right'>" & FormatNumber(CDbl(price), 2) & " " & countryCurrency & "</td>")
                    Response.Write("<td align='right'>" & Left(__date, 10) & "</td>")
                    Response.Write("<td align='right'><a href='editVehicle.aspx?vehicleID=" & vehicleID & "'>view details</a></td>")
                    Response.Write("</tr>")
                    RecSet.MoveNext()
                Loop
            End If
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            If IsDBNull(invoiceCreditAmount) = False Then
                dbOpen()
                RecSet = Conn.Execute("Select ic.leaseCurrentDate, v.vehicleID, v.regnr, v.vinNumber, v.contractNumber, v.date, p.product, pl.price, b.brand, m.model, d.dealer, (Select Sum(ap.price) From (tblInvoiceCreditContentAdditional icca INNER JOIN tblAdditionalProduct ap ON ap.additionalProductID = icca.additionalProductID) Where icca.vehicleID = v.vehicleID And icca.invoiceCreditID = ic.invoiceCreditID) As additionalProductSum From (((((((tblInvoiceCredit i INNER JOIN tblInvoiceCreditContent ic ON ic.invoiceCreditID = i.invoiceCreditID) INNER JOIN tblVehicle v ON v.vehicleID = ic.vehicleID) INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblPriceList pl ON pl.priceListID = ic.priceListID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblDealer d ON d.dealerID = v.dealerID) Where i.invoiceID = " & invoiceID & "")
                If RecSet.EOF Then
                    Response.Write("<tr><td><font color='red'>ERROR</font></td></tr>")
                Else
                    Do Until RecSet.EOF
                        leaseCurrentDate = RecSet.Fields("leaseCurrentDate").Value
                        vehicleID = RecSet.Fields("vehicleID").Value
                        regnr = RecSet.Fields("regnr").Value
                        vinNumber = RecSet.Fields("vinNumber").Value
                        contractNumber = RecSet.Fields("contractNumber").Value
                        __date = RecSet.Fields("date").Value
                        product = RecSet.Fields("product").Value
                        price = RecSet.Fields("price").Value
                        brand = RecSet.Fields("brand").Value
                        model = RecSet.Fields("model").Value
                        dealer = RecSet.Fields("dealer").Value
                        additionalProductSum = RecSet.Fields("additionalProductSum").Value
                        If IsDBNull(additionalProductSum) = True Then
                            additionalProductSum = 0
                        End If
                        leaseCurrentDate = Left(leaseCurrentDate, 7)
                        price = price + additionalProductSum
                        If rowNumber = 0 Then
                            strGrey = " bgcolor='#eeeeee'"
                            rowNumber = 1
                        Else
                            strGrey = ""
                            rowNumber = 0
                        End If
                        Response.Write("<tr" & strGrey & ">")
                        Response.Write("<td>" & dealer & "</td>")
                        Response.Write("<td>" & contractNumber & "</td>")
                        Response.Write("<td>" & regnr & "</td>")
                        Response.Write("<td>" & vinNumber & "</td>")
                        Response.Write("<td>" & product & "</td>")
                        Response.Write("<td>" & leaseCurrentDate & "</td>")
                        Response.Write("<td align='right'>- " & FormatNumber(CDbl(price), 2) & " " & countryCurrency & "</td>")
                        Response.Write("<td align='right'>" & Left(__date, 10) & "</td>")
                        Response.Write("<td align='right'><a href='editVehicle.aspx?vehicleID=" & vehicleID & "'>view details</a></td>")
                        Response.Write("</tr>")
                        RecSet.MoveNext()
                    Loop
                End If
                RecSet.Close()
                RecSet = Nothing
                dbClose()
            End If
            Response.Write("<tr><td colspan='9'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
        End If
        Response.Write("</table>")
        Under()
    End If
%>
